﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.VisualBasic;

namespace Simple.Services.System.Report.Danger
{
    public class QHSEDangerReportService
    {
        private readonly SimpleDbContext _context;
        private readonly CacheService _cacheService;
        private readonly QHSEKeyWorkSpecialtyService _keyWorkSpecialtyService;
        private readonly QHSEDangerCategoryService _dangerCategoryService;
        private readonly QHSEDictionaryService _dictionaryService;
        private readonly QHSEKeyWorkTypeService _keyWorkTypeService;
        private readonly QHSEOrganizationService _organizationService;
        private readonly QHSEViolationHSEService _dangerHSEService;
        private static List<string> SelfCheckTypes = new List<string> { "10000009", "10000010", "10000011" };
        private static List<string> SuperviseCheckTypes = new List<string> { "10000012", "10000013", "10000014" };

        private readonly IServiceScopeFactory _serviceScopeFactory;

        public QHSEDangerReportService(QHSEKeyWorkSpecialtyService keyWorkSpecialtyService, QHSEDictionaryService dictionaryService,
            QHSEKeyWorkTypeService keyWorkTypeService, QHSEViolationHSEService dangerHSEService, QHSEOrganizationService organizationService, QHSEDangerCategoryService dangerCategoryService
            , CacheService cacheService, IServiceScopeFactory serviceScopeFactory)
        {
            _keyWorkSpecialtyService = keyWorkSpecialtyService;
            _dictionaryService = dictionaryService;
            _keyWorkTypeService = keyWorkTypeService;
            _dangerHSEService = dangerHSEService;
            _organizationService = organizationService;
            _dangerCategoryService = dangerCategoryService;
            _cacheService = cacheService;
            _serviceScopeFactory = serviceScopeFactory;
        }
        private static string EscapeLikePattern(string pattern)
        {
            return pattern.Replace("'", "''").Replace("/", "//").Replace("_", "/_").Replace("%", "/%");
        }
        //获取总表
        public async Task<List<TableReport>> TableDangerTotalCheckCount(QHSETotalInputModel inputModel)
        {
            if (inputModel.TimeEnd == null || inputModel.TimeOn == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableDangerTotalCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            //返回值为一个TableReport的列表
            List<TableReport> TableReportsList = new List<TableReport>();
            using (var scope = _serviceScopeFactory.CreateAsyncScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                //筛选时间段内的隐患数据
                var query = context.Set<QHSESysDanger>().AsNoTracking().Where(u => u.CheckDate >= inputModel.TimeOn && u.CheckDate <= inputModel.TimeEnd);
                if (inputModel.OrganizationId != "00")
                {
                    //查询隐患单位ID
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        //根据组织表和隐患表查询对应的隐患
                        query = query.Join(context.Set<QHSESysOrganization>(),
                            danger => danger.OrganizationId,
                            organization => organization.OrganizationId,
                            (danger, organization) => new { Danger = danger, Organization = organization }).
                            Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/")).
                            Select(joinResult => joinResult.Danger);
                    }
                }
                //获取子单位
                var orgs = await _organizationService.GetSon(inputModel.OrganizationId, context);
                //table为最后一行统计
                TableReport table = new TableReport();
                table.OrganizationName = "总计";
                //每个子单位对应表中的一行
                foreach (var org in orgs)
                {
                    var escapedNamePath = EscapeLikePattern(org.NamePath);
                    TableReport TableReport = new TableReport();
                    TableReport.OrganizationName = org.OrganizationName;
                    //自查数量
                    TableReport.Value1 = await query.Where(u => SelfCheckTypes.Contains(u.CheckTypeCode)).
                        Join(context.Set<QHSESysOrganization>(),
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization }).
                        Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                        .CountAsync();

                    //纠察数量
                    TableReport.Value2 = await query.Where(u => SuperviseCheckTypes.Contains(u.CheckTypeCode)).
                        Join(context.Set<QHSESysOrganization>(),
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, organization) => new { Danger = danger, Organization = organization }).
                        Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                        .CountAsync();
                    table.Value1 += TableReport.Value1;
                    table.Value2 += TableReport.Value2;
                    TableReportsList.Add(TableReport);
                }
                //将表的最后一行加入链表
                TableReportsList.Add(table);
            }
            await _cacheService.SetTableDangerTotalCheckCountAsync(inputModel, TableReportsList);
            return TableReportsList;
        }

        //（2）违章查纠数量对比分析-图
        public async Task<CompareReportModel> CompareDangerDepartmentCheckCount(QHSEReportInputModel inputModel)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeOn1 == null || inputModel.TimeOn1 == null || inputModel.TimeOn2 == null || inputModel.TimeEnd2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareDangerDepartmentCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            var time1 = inputModel.TimeOn1.Value;
            var time2 = inputModel.TimeOn2.Value;
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                //原始数据
                var query = context.Set<QHSESysDanger>().Where(u => u.CheckDate >= inputModel.TimeOn1 && u.CheckDate <= inputModel.TimeEnd1).AsQueryable();
                //对比数据
                var comQuery = context.Set<QHSESysDanger>().Where(u => u.CheckDate >= inputModel.TimeOn2 && u.CheckDate <= inputModel.TimeEnd2).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedName = EscapeLikePattern(org.NamePath);
                        //原始数据
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                            danger => danger.OrganizationId,
                            organization => organization.OrganizationId,
                            (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedName}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        //对比数据
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                            danger => danger.OrganizationId,
                            organization => organization.OrganizationId,
                            (danger, organization) => new { Danger = danger, Organzition = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organzition.NamePath, $"{escapedName}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                    }
                }
                //原始数据
                var selfDanger1 = await query.Where(u => SelfCheckTypes.Contains(u.CheckTypeCode)).CountAsync();
                var superviseDanger1 = await query.Where(u => SuperviseCheckTypes.Contains(u.CheckTypeCode)).CountAsync();
                //对比数据
                var selfDanger2 = await comQuery.Where(u => SelfCheckTypes.Contains(u.CheckTypeCode)).CountAsync();
                var superviseDanger2 = await comQuery.Where(u => SuperviseCheckTypes.Contains(u.CheckTypeCode)).CountAsync();
                List<object> obj = new List<object>() { "隐患总数对比", $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };
                List<object> count1 = new List<object>() { "自查数", selfDanger1, selfDanger2 };
                List<object> count2 = new List<object>() { "监督纠察数量", superviseDanger1, superviseDanger2 };
                List<object> count3 = new List<object>() { "相差数量", superviseDanger1 - selfDanger1, superviseDanger2 - selfDanger2 };
                List<object> count4 = new List<object>() { "合计", selfDanger1 + superviseDanger1, selfDanger2 + superviseDanger2 };

                result.Item.Add(obj);
                result.Item.Add(count1);
                result.Item.Add(count2);
                result.Item.Add(count3);
                result.Item.Add(count4);
            }
            
            await _cacheService.SetCompareDangerDepartmentCheckCountAsync(inputModel, result);
            return result;
        }



        //按隐患数量分析表格
        public async Task<List<TableCompareReport>> TableDangerDepartmentCheckCount(QHSEReportInputModel inputModel)
        {
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableDangerDepartmentCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            List<TableCompareReport> TableCompareReports = new List<TableCompareReport>();
            //var data = await CompareDangerDepartmentCheckCount(inputModel);
            //if (data == null)
            //{
            //    return TableCompareReports;
            //}
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                //timeon1到timeend1内的数据
                var query = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1).AsQueryable();
                //timeon1到timeend2内的数据
                var comQuery = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                    }
                }
                //获取子单位
                var orgs = await _organizationService.GetSon(inputModel.OrganizationId, context);
                //表尾
                TableCompareReport table = new TableCompareReport();
                table.OrganizationName = "总计";
                foreach (var org in orgs)
                {
                    var escapedNamePath = EscapeLikePattern(org.NamePath);
                    TableCompareReport TableReport = new TableCompareReport();
                    TableReport.OrganizationName = org.OrganizationName;
                    //timeon1到timeend1内的自查隐患数量
                    TableReport.Value1 = await query
                        .Where(u => SelfCheckTypes.Contains(u.CheckTypeCode))
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .CountAsync();
                    //timeon2到timeend2内的自查隐患数量
                    TableReport.Value2 = await comQuery
                        .Where(u => SelfCheckTypes.Contains(u.CheckTypeCode))
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .CountAsync();


                    //timeon1到timeend1内的纠察隐患数量
                    TableReport.Value3 = await query
                        .Where(u => SuperviseCheckTypes.Contains(u.CheckTypeCode))
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .CountAsync();

                    //timeon2到timeend2内的纠察隐患数量
                    TableReport.Value4 = await comQuery
                        .Where(u => SuperviseCheckTypes.Contains(u.CheckTypeCode))
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .CountAsync();
                    //总的自查隐患数量
                    TableReport.Total1 = TableReport.Value1 + TableReport.Value2;
                    //总的纠察隐患数量
                    TableReport.Total2 = TableReport.Value3 + TableReport.Value4;


                    if (TableReport.Total2 == 0 && TableReport.Total1 == 0)
                    {
                        TableReport.GrowthRate = $"{0}%";
                    }
                    else if (TableReport.Total2 == 0 && TableReport.Total1 != 0)
                    {
                        TableReport.GrowthRate = $"{-100}%";
                    }
                    else if (TableReport.Total2 != 0 && TableReport.Total1 == 0)
                    {
                        TableReport.GrowthRate = $"{100}%";
                    }
                    else
                    {
                        TableReport.GrowthRate = $"{(TableReport.Total2 - TableReport.Total1) / TableReport.Total1 * 100}%";
                    }
                    TableCompareReports.Add(TableReport);
                    table.Value1 += TableReport.Value1;
                    table.Value2 += TableReport.Value2;
                    table.Value3 += TableReport.Value3;
                    table.Value4 += TableReport.Value4;
                    table.Total1 += TableReport.Total1;
                    table.Total2 += TableReport.Total2;
                }

                if (table.Total2 == 0 && table.Total1 == 0)
                {
                    table.GrowthRate = $"{0}%";
                }
                else if (table.Total2 == 0 && table.Total1 != 0)
                {
                    table.GrowthRate = $"{-100}%";
                }
                else if (table.Total2 != 0 && table.Total1 == 0)
                {
                    table.GrowthRate = $"{100}%";
                }
                else
                {
                    table.GrowthRate = $"{(table.Total2 - table.Total1) / table.Total1 * 100}%";
                }
                TableCompareReports.Add(table);
                await _cacheService.SetTableDangerDepartmentCheckCountAsync(inputModel, TableCompareReports);
                return TableCompareReports;
            }
        }

        public async Task<CompareReportModel> CompareSelfDangerTypeCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "隐患级别自查对比";
            return await CompareDangerTypeCheckCount(inputModel, SelfCheckTypes, type);
        }

        public async Task<CompareReportModel> CompareSuperviseDangerTypeCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "隐患级别查纠对比";
            return await CompareDangerTypeCheckCount(inputModel, SuperviseCheckTypes, type);
        }

        //隐患级别对比图
        public async Task<CompareReportModel> CompareDangerTypeCheckCount(QHSEReportInputModel inputModel, List<string> strings, string type)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareDangerTypeCheckCountAsync(inputModel, strings, type);
            if (cache != null)
            {
                return cache;
            }
            var time1 = inputModel.TimeOn1.Value;
            var time2 = inputModel.TimeOn2.Value;
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                    }
                }
                List<object> obj = new List<object>() { type, $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };

                result.Item.Add(obj);
                //获取隐患级别
                var dangerTypes = await _dictionaryService.GetDictionaryAsync("1010", context);

                foreach (var dangerType in dangerTypes)
                {
                    List<object> items = new List<object>();

                    items.Add(dangerType.Item);
                    var dan1 = await query.Where(u => u.DangerType.Equals(dangerType.Item)).CountAsync();
                    var dan2 = await comQuery.Where(u => u.DangerType.Equals(dangerType.Item)).CountAsync();
                    items.Add(dan1);
                    items.Add(dan2);
                    result.Item.Add(items);
                }
            }
            await _cacheService.SetCompareDangerTypeCheckCountAsync(inputModel, strings, type, result);
            return result;
        }

        //按隐患级别对比表格
        public async Task<List<TableTimeReport>> TableDangerTypeCheckCount(QHSEReportInputModel inputModel)
        {
            List<TableTimeReport> TableTimeReports = new List<TableTimeReport>();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableDangerTypeCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                //自查
                var query = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var query1 = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                //查纠
                var comQuery = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery1 = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    //获取隐患单位
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        query1 = query1
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);

                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery1 = comQuery1
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                    }
                }
                var dangerTypes = await _dictionaryService.GetDictionaryAsync("1010", context);

                foreach (var dangerType in dangerTypes)
                {
                    TableTimeReport table = new TableTimeReport();
                    //两个时间段内的自查数量
                    var self1 = await query.Where(u => u.DangerType.Equals(dangerType.Item)).CountAsync();
                    var self2 = await query1.Where(u => u.DangerType.Equals(dangerType.Item)).CountAsync();
                    //两个时间段内的查纠数量
                    var su1 = await comQuery.Where(u => u.DangerType.Equals(dangerType.Item)).CountAsync();
                    var su2 = await comQuery1.Where(u => u.DangerType.Equals(dangerType.Item)).CountAsync();
                    //隐患级别
                    table.CompareType = dangerType.Item;
                    table.SelfValue1 = self1;
                    table.SelfValue2 = self2;
                    table.SuperviseValue1 = su1;
                    table.SuperviseValue2 = su2;
                    TableTimeReports.Add(table);
                }
            }
            await _cacheService.SetTableDangerTypeCheckCountAsync(inputModel, TableTimeReports);
            return TableTimeReports;
        }

        //隐患类别自查对比
        public async Task<CompareReportModel> CompareSelfDangerCategoryCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "隐患类别自查对比";
            return await CompareDangerCategoryCheckCount(inputModel, SelfCheckTypes, type);
        }

        //隐患类别查纠对比
        public async Task<CompareReportModel> CompareSuperviseDangerCategoryCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "隐患类别查纠对比";
            return await CompareDangerCategoryCheckCount(inputModel, SuperviseCheckTypes, type);
        }

        //隐患类别对比
        public async Task<CompareReportModel> CompareDangerCategoryCheckCount(QHSEReportInputModel inputModel, List<string> strings, string type)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeOn1 == null || inputModel.TimeEnd1 == null || inputModel.TimeOn2 == null || inputModel.TimeEnd2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareDangerCategoryCheckCountAsync(inputModel, strings, type);
            if (cache != null)
            {
                return cache;
            }
            var time1 = inputModel.TimeOn1.Value;
            var time2 = inputModel.TimeOn2.Value;
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysDanger>().Where(u => u.CheckDate >= inputModel.TimeOn1 && u.CheckDate <= inputModel.TimeEnd1 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysDanger>().Where(u => u.CheckDate >= inputModel.TimeOn2 && u.CheckDate <= inputModel.TimeEnd2 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedName = EscapeLikePattern(org.NamePath);
                        query = query
                                .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, orgnization) => new { Danger = danger, Orgnization = orgnization })
                                .Where(joinResult => EF.Functions.Like(joinResult.Orgnization.NamePath, $"{escapedName}%", "/"))
                                .Select(joinResult => joinResult.Danger);

                        comQuery = comQuery
                        .Join(context.Set<QHSESysOrganization>(),
                        danger => danger.OrganizationId,
                        organization => organization.OrganizationId,
                        (danger, orgnization) => new { Danger = danger, Orgnization = orgnization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Orgnization.NamePath, $"{escapedName}%", "/"))
                        .Select(joinResult => joinResult.Danger);
                    }
                }                
                List<object> obj = new List<object>() { type, $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };
                result.Item.Add(obj);



                var dangerCategorys = await _dangerCategoryService.GetSon("不安全因素", context);
                foreach (var dangerCategory in dangerCategorys)
                {

                    List<object> items = new List<object>();
                    items.Add(dangerCategory);
                    var danger1 = await GetDangerCountsAsync(query, dangerCategory, context);
                    var danger2 = await GetDangerCountsAsync(comQuery, dangerCategory, context);
                    items.Add(danger1);
                    items.Add(danger2);
                    result.Item.Add(items);
                }

            }
            await _cacheService.SetCompareDangerCategoryCheckCountAsync(inputModel, strings, type, result);
            return result;
        }

        //隐患按类别对比表格
        public async Task<List<TableTimeReport>> TableDangerCategoryCheckCount(QHSEReportInputModel inputModel)
        {
            List<TableTimeReport> TableTimeReports = new List<TableTimeReport>();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableDangerCategoryCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                //自查
                var query = context.Set<QHSESysDanger>().Where(u => u.CheckDate >= inputModel.TimeOn1 && u.CheckDate <= inputModel.TimeEnd1 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var query1 = context.Set<QHSESysDanger>().Where(u => u.CheckDate >= inputModel.TimeOn2 && u.CheckDate <= inputModel.TimeEnd2 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();

                //查纠
                var comQuery = context.Set<QHSESysDanger>().Where(u => u.CheckDate >= inputModel.TimeOn1 && u.CheckDate <= inputModel.TimeEnd1 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery1 = context.Set<QHSESysDanger>().Where(u => u.CheckDate >= inputModel.TimeOn2 && u.CheckDate <= inputModel.TimeEnd2 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();


                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedName = EscapeLikePattern(org.NamePath);
                        query = query
                                .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                orgnization => orgnization.OrganizationId,
                                (danger, orgnization) => new { Danger = danger, Organzation = orgnization })
                                .Where(joinResult => EF.Functions.Like(joinResult.Organzation.NamePath, $"%{escapedName}%", "/"))
                                .Select(joinResult => joinResult.Danger);
                        query1 = query1
                                .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                orgnization => orgnization.OrganizationId,
                                (danger, orgnization) => new { Danger = danger, Organzation = orgnization })
                                .Where(joinResult => EF.Functions.Like(joinResult.Organzation.NamePath, $"%{escapedName}%", "/"))
                                .Select(joinResult => joinResult.Danger);
                        comQuery = comQuery
                                .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                orgnization => orgnization.OrganizationId,
                                (danger, orgnization) => new { Danger = danger, Organzation = orgnization })
                                .Where(joinResult => EF.Functions.Like(joinResult.Organzation.NamePath, $"%{escapedName}%", "/"))
                                .Select(joinResult => joinResult.Danger);
                        comQuery1 = comQuery
                                .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                orgnization => orgnization.OrganizationId,
                                (danger, orgnization) => new { Danger = danger, Organzation = orgnization })
                                .Where(joinResult => EF.Functions.Like(joinResult.Organzation.NamePath, $"%{escapedName}%", "/"))
                                .Select(joinResult => joinResult.Danger);
                    }
                }
                var sons = await _dangerCategoryService.GetSon("不安全因素", context);
                foreach (var dangerCategroy in sons)
                {
                    TableTimeReport table = new TableTimeReport();
                    table.CompareType = dangerCategroy;
                    var self1 = await GetDangerCountsAsync(query, dangerCategroy, context);
                    var self2 = await GetDangerCountsAsync(query1, dangerCategroy, context);
                    var su1 = await GetDangerCountsAsync(comQuery, dangerCategroy, context);
                    var su2 = await GetDangerCountsAsync(comQuery1, dangerCategroy, context);
                    table.CompareType = dangerCategroy;
                    table.SelfValue1 = self1;
                    table.SelfValue2 = self2;
                    table.SuperviseValue1 = su1;
                    table.SuperviseValue2 = su2;
                    TableTimeReports.Add(table);
                }
            }
            await _cacheService.SetTableDangerCategoryCheckCountAsync(inputModel, TableTimeReports);
            return TableTimeReports;
        }
        //计算对应隐患类别的个数
        private async Task<int> GetDangerCountsAsync(IQueryable<QHSESysDanger> query, string dangerCategory, SimpleDbContext context)
        {

            var children = await _dangerCategoryService.GetChildren(dangerCategory, context);
            var count = await query
                .Where(v => children.Contains(v.DangerCategory))
                .CountAsync();
            return count;
        }
        public async Task<CompareReportModel> CompareSelfDangerWorkSpecialtyCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "专业类别自查对比";
            return await CompareDangerWorkSpecialtyCheckCount(inputModel, SelfCheckTypes, type);

        }
        public async Task<CompareReportModel> CompareSuperviseDangerWorkSpecialtyCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "专业类别监督对比";
            return await CompareDangerWorkSpecialtyCheckCount(inputModel, SuperviseCheckTypes, type);
        }
        public async Task<CompareReportModel> CompareDangerWorkSpecialtyCheckCount(QHSEReportInputModel inputModel, List<string> strings, string type)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeOn1 == null || inputModel.TimeEnd1 == null || inputModel.TimeOn2 == null || inputModel.TimeEnd2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareDangerSpecialtyCheckCountAsync(inputModel, strings, type);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                    }
                }
                var time1 = inputModel.TimeOn1.Value;
                var time2 = inputModel.TimeOn2.Value;
                List<object> obj = new List<object>() { type, $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };

                result.Item.Add(obj);
                var specialties = await _keyWorkSpecialtyService.GetAsync(context);
                foreach (var specialty in specialties)
                {
                    List<object> items = new List<object>();
                    items.Add(specialty.Specialty);
                    var danger1 = await query.Where(u => u.WorkSpecialty.Equals(specialty.Specialty)).CountAsync();
                    var danger2 = await comQuery.Where(u => u.WorkSpecialty.Equals(specialty.Specialty)).CountAsync();
                    items.Add(danger1);
                    items.Add(danger2);
                    result.Item.Add(items);
                }
            }
            await _cacheService.SetCompareDangerSpecialtyCheckCountAsync(inputModel, strings, type, result);
            return result;
        }
        public async Task<List<TableTimeReport>> TableDangerWorkSpecialtyCheckCount(QHSEReportInputModel inputModel)
        {
            List<TableTimeReport> TableTimeReports = new List<TableTimeReport>();
            if (inputModel.TimeOn1 == null || inputModel.TimeEnd1 == null || inputModel.TimeOn2 == null || inputModel.TimeEnd2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableDangerSpecialtyCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var query1 = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery1 = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        query1 = query1
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery1 = comQuery1
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                    }
                }
                var specialties = await _keyWorkSpecialtyService.GetAsync(context);
                foreach (var specialty in specialties)
                {
                    TableTimeReport table = new TableTimeReport();
                    var self1 = await query.Where(u => u.WorkSpecialty.Equals(specialty.Specialty)).CountAsync();
                    var self2 = await query1.Where(u => u.WorkSpecialty.Equals(specialty.Specialty)).CountAsync();
                    var su1 = await comQuery.Where(u => u.WorkSpecialty.Equals(specialty.Specialty)).CountAsync();
                    var su2 = await comQuery1.Where(u => u.WorkSpecialty.Equals(specialty.Specialty)).CountAsync();
                    table.CompareType = specialty.Specialty;
                    table.SelfValue1 = self1;
                    table.SelfValue2 = self2;
                    table.SuperviseValue1 = su1;
                    table.SuperviseValue2 = su2;
                    TableTimeReports.Add(table);
                }
                TableTimeReports = TableTimeReports.OrderByDescending(TableTimeReports => TableTimeReports.SelfValue1).ToList();
            }
            
            await _cacheService.SetTableDangerSpecialtyCheckCountAsync(inputModel, TableTimeReports);
            return TableTimeReports;
        }
        public async Task<CompareReportModel> CompareSelfDangerDepartmentCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "归属直线部门自查对比";
            return await CompareDangerDepartmentCheckCount(inputModel, SelfCheckTypes, type);

        }
        public async Task<CompareReportModel> CompareSuperviseDangerDepartmentCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "归属直线部门监督对比";
            return await CompareDangerDepartmentCheckCount(inputModel, SuperviseCheckTypes, type);
        }
        public async Task<CompareReportModel> CompareDangerDepartmentCheckCount(QHSEReportInputModel inputModel, List<string> strings, string type)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeOn1 == null || inputModel.TimeEnd1 == null || inputModel.TimeOn2 == null || inputModel.TimeEnd2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareDangerDirectDepartmentCheckCountAsync(inputModel, strings, type);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                    }
                }
                var time1 = inputModel.TimeOn1.Value;
                var time2 = inputModel.TimeOn2.Value;
                List<object> obj = new List<object>() { type, $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };

                result.Item.Add(obj);
                var departments = await _dictionaryService.GetDictionaryAsync("1008", context);
                foreach (var department in departments)
                {
                    List<object> items = new List<object>();
                    items.Add(department.Item);
                    var danger1 = await query.Where(u => u.DirectDepartment.Equals(department.Item)).CountAsync();
                    var danger2 = await comQuery.Where(u => u.DirectDepartment.Equals(department.Item)).CountAsync();
                    items.Add(danger1);
                    items.Add(danger2);
                    result.Item.Add(items);
                }
            }
            await _cacheService.SetCompareDangerDirectDepartmentCheckCountAsync(inputModel, strings, type, result);
            return result;
        }
        public async Task<List<TableTimeReport>> TableDangerDirectDepartmentCheckCount(QHSEReportInputModel inputModel)
        {
            List<TableTimeReport> TableTimeReports = new List<TableTimeReport>();
            if (inputModel.TimeOn1 == null || inputModel.TimeEnd1 == null || inputModel.TimeOn2 == null || inputModel.TimeEnd2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableDangerDirectDepartmentCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var query1 = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery1 = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        query1 = query1
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery1 = comQuery1
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                    }
                }
                var departmens = await _dictionaryService.GetDictionaryAsync("1008", context);
                foreach (var department in departmens)
                {
                    TableTimeReport table = new TableTimeReport();
                    var self1 = await query.Where(u => u.DirectDepartment.Equals(department.Item)).CountAsync();
                    var self2 = await query1.Where(u => u.DirectDepartment.Equals(department.Item)).CountAsync();
                    var su1 = await comQuery.Where(u => u.DirectDepartment.Equals(department.Item)).CountAsync();
                    var su2 = await comQuery1.Where(u => u.DirectDepartment.Equals(department.Item)).CountAsync();
                    table.CompareType = department.Item;
                    table.SelfValue1 = self1;
                    table.SelfValue2 = self2;
                    table.SuperviseValue1 = su1;
                    table.SuperviseValue2 = su2;
                    TableTimeReports.Add(table);
                }
                TableTimeReports = TableTimeReports.OrderByDescending(TableTimeReports => TableTimeReports.SelfValue1).ToList();
            }
            await _cacheService.SetTableDangerDirectDepartmentCheckCountAsync(inputModel, TableTimeReports);
            return TableTimeReports;
        }
        public async Task<CompareReportModel> CompareSelfDangerKeyWorkCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "关键作业自查对比";
            return await CompareDangerKeyWorkCheckCount(inputModel, SelfCheckTypes, type);

        }
        public async Task<CompareReportModel> CompareSuperviseDangerKeyWorkCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "关键作业监督对比";
            return await CompareDangerKeyWorkCheckCount(inputModel, SuperviseCheckTypes, type);
        }
        public async Task<CompareReportModel> CompareDangerKeyWorkCheckCount(QHSEReportInputModel inputModel, List<string> strings, string type)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeOn1 == null || inputModel.TimeEnd1 == null || inputModel.TimeOn2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareDangerKeyWorkCheckCountAsync(inputModel, strings, type);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                    }
                }
                var time1 = inputModel.TimeOn1.Value;
                var time2 = inputModel.TimeOn2.Value;
                List<object> obj = new List<object>() { type, $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };

                result.Item.Add(obj);
                var specialties = await _keyWorkTypeService.GetAsync(context);
                // 使用 LINQ GroupBy 去重
                var distincSpecialties = specialties.GroupBy(s => s.WorkType)
                                                    .Select(g => g.First())
                                                    .ToList();
                foreach (var specialty in distincSpecialties)
                {
                    List<object> items = new List<object>();
                    items.Add(specialty.WorkType);
                    var danger1 = await query.Where(u => u.WorkType.Equals(specialty.WorkType)).CountAsync();
                    var danger2 = await comQuery.Where(u => u.WorkType.Equals(specialty.WorkType)).CountAsync();
                    items.Add(danger1);
                    items.Add(danger2);
                    result.Item.Add(items);
                }
            }
            await _cacheService.SetCompareDangerKeyWorkCheckCountAsync(inputModel, strings, type, result);
            return result;
        }
        public async Task<List<TableTimeReport>> TableDangerKeyWorkCheckCount(QHSEReportInputModel inputModel)
        {
            List<TableTimeReport> TableTimeReports = new List<TableTimeReport>();
            if (inputModel.TimeOn1 == null || inputModel.TimeEnd1 == null || inputModel.TimeOn2 == null || inputModel.TimeEnd2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableDangerKeyWorkCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var query1 = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery1 = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        query1 = query1
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery1 = comQuery1
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                    }
                }
                var specialties = await _keyWorkTypeService.GetAsync(context);
                // 使用 LINQ GroupBy 去重
                var distinctSpecialties = specialties.GroupBy(s => s.WorkType)
                                                     .Select(g => g.First())
                                                     .ToList();
                foreach (var specialty in distinctSpecialties)
                {
                    TableTimeReport table = new TableTimeReport();
                    var self1 = await query.Where(u => u.WorkType.Equals(specialty.WorkType)).CountAsync();
                    var self2 = await query1.Where(u => u.WorkType.Equals(specialty.WorkType)).CountAsync();
                    var su1 = await comQuery.Where(u => u.WorkType.Equals(specialty.WorkType)).CountAsync();
                    var su2 = await comQuery1.Where(u => u.WorkType.Equals(specialty.WorkType)).CountAsync();
                    table.CompareType = specialty.WorkType;
                    table.SelfValue1 = self1;
                    table.SelfValue2 = self2;
                    table.SuperviseValue1 = su1;
                    table.SuperviseValue2 = su2;
                    TableTimeReports.Add(table);
                }
                TableTimeReports = TableTimeReports.OrderByDescending(TableTimeReports => TableTimeReports.SelfValue1).ToList();
            }
            await _cacheService.SetTableDangerKeyWorkCheckCountAsync(inputModel, TableTimeReports);
            return TableTimeReports;
        }
        public async Task<CompareReportModel> CompareSelfDangerHSECheckCount(QHSEReportInputModel inputModel)
        {
            string type = "体系要素自查对比";
            return await CompareDangerHSECheckCount(inputModel, SelfCheckTypes, type);

        }
        public async Task<CompareReportModel> CompareSuperviseDangerHSECheckCount(QHSEReportInputModel inputModel)
        {
            string type = "体系要素监督对比";
            return await CompareDangerHSECheckCount(inputModel, SuperviseCheckTypes, type);
        }
        public async Task<CompareReportModel> CompareDangerHSECheckCount(QHSEReportInputModel inputModel, List<string> strings, string type)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeOn1 == null || inputModel.TimeEnd1 == null || inputModel.TimeOn2 == null || inputModel.TimeEnd2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareDangerHSECheckCountAsync(inputModel, strings, type);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                    }
                }
                var time1 = inputModel.TimeOn1.Value;
                var time2 = inputModel.TimeOn2.Value;
                List<object> obj = new List<object>() { type, $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };

                result.Item.Add(obj);
                var specialties = await _dangerHSEService.GetAsync(context);
                foreach (var specialty in specialties)
                {
                    List<object> items = new List<object>();
                    items.Add(specialty.Item);
                    var danger1 = await query.Where(u => u.HseElement.Equals(specialty.Item)).CountAsync();
                    var danger2 = await comQuery.Where(u => u.HseElement.Equals(specialty.Item)).CountAsync();
                    items.Add(danger1);
                    items.Add(danger2);
                    result.Item.Add(items);
                }
            }
            await _cacheService.SetCompareDangerHSECheckCountAsync(inputModel, strings, type, result);
            return result;
        }
        public async Task<List<TableTimeReport>> TableDangerHSECheckCount(QHSEReportInputModel inputModel)
        {
            List<TableTimeReport> TableTimeReports = new List<TableTimeReport>();
            if (inputModel.TimeOn1 == null || inputModel.TimeEnd1 == null || inputModel.TimeOn2 == null || inputModel.TimeEnd2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableDangerHSECheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var query1 = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery1 = context.Set<QHSESysDanger>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        query1 = query1
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                        comQuery1 = comQuery1
                            .Join(context.Set<QHSESysOrganization>(),
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Danger);
                    }
                }
                var specialties = await _dangerHSEService.GetAsync(context);
                foreach (var specialty in specialties)
                {
                    TableTimeReport table = new TableTimeReport();
                    var self1 = await query.Where(u => u.HseElement.Equals(specialty.Item)).CountAsync();
                    var self2 = await query1.Where(u => u.HseElement.Equals(specialty.Item)).CountAsync();
                    var su1 = await comQuery.Where(u => u.HseElement.Equals(specialty.Item)).CountAsync();
                    var su2 = await comQuery1.Where(u => u.HseElement.Equals(specialty.Item)).CountAsync();
                    table.CompareType = specialty.Item;
                    table.SelfValue1 = self1;
                    table.SelfValue2 = self2;
                    table.SuperviseValue1 = su1;
                    table.SuperviseValue2 = su2;
                    TableTimeReports.Add(table);
                }
                TableTimeReports = TableTimeReports.OrderByDescending(TableTimeReports => TableTimeReports.SelfValue1).ToList();
            }
            await _cacheService.SetTableDangerHSECheckCountAsync(inputModel, TableTimeReports);
            return TableTimeReports;
        }
    }
}
